// db.js
import sqlite3 from 'sqlite3'
import { app, dialog } from 'electron'
import { join } from 'path'
import fs from 'fs'
import dealer from './dealer'
import dealerLevel from './dealer_level'
import dividend from './dividend'
import performance from './performance'
import dividend_list from './dividend_list'
import logger from './logger'
import dealer_level_change from './dealer_level_change'
import preformance_record from './preformance_record'

const dbPath = join(app.getPath('userData'), 'master.db')
console.log(dbPath)

const sqlite = new sqlite3.Database(dbPath, (err) => {
  if (err) {
    console.error('Error opening database:', err.message)
  } else {
    console.log('Connected to SQLite database.')
    initializeTables()
  }
})

// 初始化表的函数，可以在这里创建多个表
function initializeTables() {
  //创建经销商等级表
  sqlite.run(
    `
    CREATE TABLE IF NOT EXISTS dealer_level (
      dealer_level_id INTEGER PRIMARY KEY AUTOINCREMENT,
      dealer_level_name TEXT NOT NULL,
      is_dividend INTEGER NOT NULL
    )
  `,
    (err) => {
      if (err) {
        console.error('Error creating dealer_level table:', err.message)
      } else {
        console.log('dealer_level table created successfully.')
      }
    }
  )

  //创建经销商表
  sqlite.run(
    `
    CREATE TABLE IF NOT EXISTS dealer (
      dealer_id INTEGER PRIMARY KEY AUTOINCREMENT,
      parent_dealer_id INTEGER,
      parent_dealer_name TEXT,
      dealer_level_id INTEGER,
      dealer_level_name TEXT,
      dealer_name TEXT,
      dealer_address TEXT,
      dealer_mobile TEXT,
      dealer_remark TEXT,
      create_time TEXT default(datetime(CURRENT_TIMESTAMP,'localtime'))
    )
  `,
    (err) => {
      if (err) {
        console.error('Error creating dealer table:', err.message)
      } else {
        console.log('dealer table created successfully.')
        // 添加缺失字段函数
        const addColumnIfNotExists = (columnName, columnType) => {
          const checkColumnSql = `PRAGMA table_info(dealer)`
          sqlite.all(checkColumnSql, [], (err, rows) => {
            if (err) {
              console.error(`Error checking column ${columnName}:`, err.message)
              return
            }

            const columnExists = rows.some((row) => row.name === columnName)

            if (!columnExists) {
              const alterTableSql = `ALTER TABLE dealer ADD COLUMN ${columnName} ${columnType} default 0`
              sqlite.run(alterTableSql, (err) => {
                if (err) {
                  console.error(`Error adding ${columnName} column:`, err.message)
                } else {
                  console.log(`Column ${columnName} added successfully.`)
                }
              })
            } else {
              console.log(`Column ${columnName} already exists.`)
            }
          })
        }

        // 
        addColumnIfNotExists('is_delete', 'INTEGER')
      }
    }
  )
  //创建分红规则表
  sqlite.run(
    `
    CREATE TABLE IF NOT EXISTS dividend (
      dividend_id INTEGER PRIMARY KEY AUTOINCREMENT,
      dividend_name TEXT,
      is_enable INTEGER,
      create_time TEXT
    )
  `,
    (err) => {
      if (err) {
        console.error('Error creating dividend table:', err.message)
      } else {
        console.log('dividend table created successfully.')
      }
    }
  )
  //创建分红比例表
  sqlite.run(
    `
    CREATE TABLE IF NOT EXISTS dividend_ratio (
      dividend_ratio_id INTEGER PRIMARY KEY AUTOINCREMENT,
      dividend_id INTEGER,
      start_money INTEGER,
      end_money INTEGER,
      ratio REAL,
      create_time TEXT
    )
  `,
    (err) => {
      if (err) {
        console.error('Error creating dividend table:', err.message)
      } else {
        console.log('dividend table created successfully.')
      }
    }
  )

  //创建业绩
  sqlite.run(
    `
    CREATE TABLE IF NOT EXISTS performance (
      performance_id INTEGER PRIMARY KEY AUTOINCREMENT,
      dealer_id INTEGER,
      dealer_name TEXT,
      dealer_level_id INTEGER,
      dealer_level_name TEXT,
      performance_date TEXT,
      performance_money REAL,
      create_time TEXT,
      modify_time TEXT
    )
  `,
    (err) => {
      if (err) {
        console.error('Error creating performance table:', err.message)
      } else {
        console.log('performance table created successfully.')
        // 添加缺失字段函数
        const addColumnIfNotExists = (columnName, columnType) => {
          const checkColumnSql = `PRAGMA table_info(performance)`
          sqlite.all(checkColumnSql, [], (err, rows) => {
            if (err) {
              console.error(`Error checking column ${columnName}:`, err.message)
              return
            }

            const columnExists = rows.some((row) => row.name === columnName)

            if (!columnExists) {
              const alterTableSql = `ALTER TABLE performance ADD COLUMN ${columnName} ${columnType}`
              sqlite.run(alterTableSql, (err) => {
                if (err) {
                  console.error(`Error adding ${columnName} column:`, err.message)
                } else {
                  console.log(`Column ${columnName} added successfully.`)
                }
              })
            } else {
              console.log(`Column ${columnName} already exists.`)
            }
          })
        }

        // 添加 parent_dealer_id 和 parent_dealer_name 字段
        addColumnIfNotExists('parent_dealer_id', 'INTEGER')
        addColumnIfNotExists('parent_dealer_name', 'TEXT')
      }
    }
  )

  //创建业绩-记录表
  sqlite.run(
    `
    CREATE TABLE IF NOT EXISTS performance_record (
      performance_record_id INTEGER PRIMARY KEY AUTOINCREMENT,
      dealer_id INTEGER,
      dealer_name TEXT,
      performance_date TEXT,
      team_money REAL,
      self_money REAL,
      create_time TEXT,
      modify_time TEXT
    )
  `,
    (err) => {
      if (err) {
        console.error('Error creating performance_record table:', err.message)
      } else {
        console.log('performance_record table created successfully.')
      }
    }
  )

  //经销商变更-记录表
  sqlite.run(
    `
    CREATE TABLE IF NOT EXISTS dealer_level_change (
      dealer_level_change_id INTEGER PRIMARY KEY AUTOINCREMENT,
      dealer_id INTEGER,
      dealer_name TEXT,
      change_date TEXT,
      create_time TEXT
    )
  `,
    (err) => {
      if (err) {
        console.error('Error creating dealer_level_change table:', err.message)
      } else {
        console.log('dealer_level_change table created successfully.')
      }
    }
  )

  //创建日志表
  sqlite.run(
    `
    CREATE TABLE IF NOT EXISTS logger (
      logger_id INTEGER PRIMARY KEY AUTOINCREMENT,
      logger_msg TEXT NOT NULL,
      create_time TEXT
    )
  `,
    (err) => {
      if (err) {
        console.error('Error creating logger table:', err.message)
      } else {
        console.log('logger table created successfully.')
      }
    }
  )
}

// 封装数据库操作到 db 对象
const db = {
  ...dealer(sqlite),
  ...dealerLevel(sqlite),
  ...dividend(sqlite),
  ...performance(sqlite),
  ...dividend_list(sqlite),
  ...logger(sqlite),
  ...preformance_record(sqlite),
  ...dealer_level_change(sqlite)
}

// 处理选择文件夹
export async function selectBackupFolder() {
  const result = await dialog.showOpenDialog({
    properties: ['openDirectory']
  })
  if (result.canceled) {
    return null // 用户取消了选择
  } else {
    return result.filePaths[0] // 返回选择的文件夹路径
  }
}
// 动态备份数据库的函数，使用传入的路径
export function backupDatabase(event, backupDir) {
  // 如果备份目录不存在，则创建它
  if (!fs.existsSync(backupDir)) {
    fs.mkdirSync(backupDir, { recursive: true })
  }

  // 生成备份文件名
  const backupPath = join(backupDir, `master-backup-${Date.now()}.db`)

  // 执行文件复制
  return new Promise((resolve, reject) => {
    fs.copyFile(dbPath, backupPath, (err) => {
      if (err) {
        console.error('Failed to backup database:', err)
        reject(err.message)
      } else {
        console.log('Database backed up successfully to:', backupPath)
        resolve(`Backup successful: ${backupPath}`)
      }
    })
  })
}

// 导入数据
export async function selectDatabaseFile() {
  const result = await dialog.showOpenDialog({
    properties: ['openFile'],
    filters: [{ name: 'Database Files', extensions: ['db'] }]
  })
  if (result.canceled) {
    return null // 用户取消了选择
  } else {
    return result.filePaths[0] // 返回选择的文件路径
  }
}
export function importDatabase(event, selectedDbPath) {
  return new Promise((resolve, reject) => {
    fs.copyFile(selectedDbPath, dbPath, (err) => {
      if (err) {
        console.error('Error importing database:', err.message)
        reject(`导入失败:${err.message}`)
      } else {
        resolve('导入成功')
      }
    })
  })
}

//清除所有数据
export function clearDatabase() {
  return new Promise((resolve, reject) => {
    // 获取所有表名
    const getTablesSql = `
      SELECT name
      FROM sqlite_master
      WHERE type = 'table'
        AND name NOT LIKE 'sqlite_%';
    `

    sqlite.all(getTablesSql, [], (err, tables) => {
      if (err) {
        console.error('Error fetching table names:', err.message)
        reject('Error fetching table names')
        return
      }

      if (tables.length === 0) {
        resolve('No tables to clear.')
        return
      }

      // 开启事务
      sqlite.run('BEGIN TRANSACTION', (err) => {
        if (err) {
          console.error('Error starting transaction:', err.message)
          reject('Error starting transaction')
          return
        }

        // 清空所有表的数据
        const clearTasks = tables.map((table) => {
          return new Promise((resolve, reject) => {
            const clearTableSql = `DELETE FROM ${table.name};`
            sqlite.run(clearTableSql, [], (err) => {
              if (err) {
                console.error(`Error clearing table ${table.name}:`, err.message)
                reject(`Error clearing table ${table.name}`)
              } else {
                console.log(`Table ${table.name} cleared successfully.`)
                resolve()
              }
            })
          })
        })

        // 执行清空任务
        Promise.all(clearTasks)
          .then(() => {
            // 提交事务
            sqlite.run('COMMIT', (err) => {
              if (err) {
                console.error('Error committing transaction:', err.message)
                reject('Error committing transaction.')
              } else {
                resolve('Database cleared successfully.')
              }
            })
          })
          .catch((error) => {
            // 回滚事务
            sqlite.run('ROLLBACK', () => {
              reject(error)
            })
          })
      })
    })
  })
}

export default db
